/*
 * Created by: 
 * Created: 23 November 2006
 */
//---------------------------------------------------------------------
// File: EntLibDatabaseHelper.cs


using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;


namespace BizUnit.Extensions.EntLib.Utilities
{
    public class EntLibDatabaseHelper
    {

        #region constructor(s)

        /// <summary>
        /// Constructor for class, default constructor is private to prevent instances being
        /// created as the class only has static methods
        /// </summary>
        private EntLibDatabaseHelper()
        {
        }

        static EntLibDatabaseHelper()
        {
            LoadTypeHashTable();
        }

        #endregion // constructor(s)

        private static Hashtable DatabaseDBType;

        #region Static Methods
        /// <summary>
        /// Executes the SQL statement against the database and returns a DataSet with the results
        /// </summary>
        /// <param name="configFileLocation">Location of the Database configuration file used by EntLib. This can be a relative path e.g. ..\..\db.config</param>
        /// <param name="databaseConfigName">Name of the database to be used in the configuration file e.g. configuration key is Northwind</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>DataSet with the results of the executed command</returns>
        public static DataSet ExecuteSqlCommand(string configFileLocation, string databaseConfigName, string sqlCommand)
        {
            Database db = GetDatabase(configFileLocation, databaseConfigName);
            return db.ExecuteDataSet(CommandType.Text, sqlCommand);
        }

        /// <summary>
        /// Executes the SQL statement and returns the first column of the first row in the resultset returned by the query.
        /// </summary>
        /// <param name="configFileLocation">Location of the Database configuration file used by EntLib. This can be a relative path e.g. ..\..\db.config</param>
        /// <param name="databaseConfigName">Name of the database to be used in the configuration file e.g. configuration key is Northwind</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>The number of rows affected by the operation</returns>
        public static int ExecuteScalar(string configFileLocation, string databaseConfigName, string sqlCommand)
        {
            Database db = GetDatabase(configFileLocation, databaseConfigName);
            return Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sqlCommand));
        }

        /// <summary>
        /// Executes the SQL statement
        /// </summary>
        /// <param name="configFileLocation">Location of the Database configuration file used by EntLib. This can be a relative path e.g. ..\..\db.config</param>
        /// <param name="databaseConfigName">Name of the database to be used in the configuration file e.g. configuration key is Northwind</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// /// <returns>The contents of the first column of the first row in the resultset</returns>
        public static int ExecuteNonQuery(string configFileLocation, string databaseConfigName, string sqlCommand)
        {
            Database db = GetDatabase(configFileLocation, databaseConfigName);
            return db.ExecuteNonQuery(CommandType.Text, sqlCommand);
        }

        public static Database GetDatabase(string configFileLocation, string databaseConfigName)
        {
            IConfigurationSource dataSource = new FileConfigurationSource(configFileLocation);
            DatabaseProviderFactory dbFactory = new DatabaseProviderFactory(dataSource);

            return dbFactory.Create(databaseConfigName);
        }

        public static string BuildSQLQuery(XmlNode queryConfig, Context context)
        {
            string rawSQLQuery = context.ReadConfigAsString(queryConfig, "RawSQLQuery");
            XmlNodeList sqlParams = queryConfig.SelectNodes("SQLQueryParams/*");

            if (null != sqlParams)
            {
                ArrayList paramArray = new ArrayList();
                //context

                foreach (XmlNode sqlParam in sqlParams)
                {
                    string p = context.ReadConfigAsString(sqlParam, ".");
                    paramArray.Add(p);
                }

                object[] paramObjs = (object[])paramArray.ToArray(typeof(object));
                return string.Format(rawSQLQuery, paramObjs);
            }
            else
            {
                return rawSQLQuery;
            }
        }

        public static DataSet GetDataSetForExport(string tableNames, string configFileLocation, string databaseConfigName)
        {
            string[] arrtableNames = tableNames.Split(new char[] { ',' });
            DataSet container = new DataSet("DataStore");
            DataSet ds;
            foreach (string s in arrtableNames)
            {
                ds = ExecuteSqlCommand(configFileLocation, databaseConfigName, BuildSQL(s));
                container.Tables.Add(s);
                container.Tables[s].Merge(ds.Tables[0]);
            }
            return container;
        }

        private static string BuildSQL(string tableName)
        {
            string selectStar = "select * from ";
            StringBuilder sb = new StringBuilder();
            sb.Append(selectStar);
            sb.Append(tableName);
            return sb.ToString();
        }

        private static void LoadTypeHashTable()
        {
            DatabaseDBType = new Hashtable();
            DatabaseDBType.Add(typeof(bool).FullName, DbType.Boolean);
            DatabaseDBType.Add(typeof(int).FullName, DbType.Int32);
            DatabaseDBType.Add(typeof(string).FullName, DbType.AnsiString);
            DatabaseDBType.Add(typeof(DateTime).FullName, DbType.DateTime);
            DatabaseDBType.Add(typeof(Int16).FullName, DbType.Int16);
            DatabaseDBType.Add(typeof(Guid).FullName, DbType.Guid);
            DatabaseDBType.Add(typeof(Byte[]).FullName, DbType.Binary);
        }

        public static DataSet GetDataSet(string datasetSchemaFile, string datasetDataFile, double delayBetweenRecordImport)
        {
            //replace the getdate() function in the time place with the current date
            DateTime dt = DateTime.Now;
            StreamReader sr = new StreamReader(datasetDataFile);
            string fileContents = sr.ReadToEnd();
            sr.Close();

            int startIndex = 0;
            bool matchExists = true;
            Hashtable htGetDateIndices = new Hashtable();

            while (matchExists)
            {
                int index = fileContents.IndexOf("getdate()", startIndex, fileContents.Length - startIndex);
                if (index == -1)
                {
                    matchExists = false;
                }
                else
                {
                    dt = dt.AddMinutes(delayBetweenRecordImport);
                    string pattern = index.ToString() + "getdate()" + dt.ToString();
                    htGetDateIndices.Add(pattern, dt.GetDateTimeFormats('s')[0]);
                    fileContents = fileContents.Remove(index, 9); //9 is the length of the string "getdate()"
                    fileContents = fileContents.Insert(index, pattern);
                    startIndex = index + 9; //move few positions forward to being the search again. 9 is a logical fit as getdate() is 9 characters
                }
            }

            //for each found key, simply replace the corresponding match in filecontents
            foreach (string getDateIndex in htGetDateIndices.Keys)
            {
                fileContents = fileContents.Replace(getDateIndex, htGetDateIndices[getDateIndex].ToString());
            }

            StringReader stringReader = new StringReader(fileContents);
            DataSet ds = new DataSet("DataStore");
            ds.ReadXmlSchema(datasetSchemaFile);
            ds.ReadXml(stringReader);

            stringReader.Close();
            return ds;
        }

        public static void ImportDatasetDataIntoDBTable(string configFileLocation, string databaseConfigName, DataSet dsToImport)
        {
            //TODO: I don't like the way this is done, it's on the list for refactoring ;0)
            DataSet ds = new DataSet();
            Database db = GetDatabase(configFileLocation, databaseConfigName);
            ds.Merge(dsToImport);

            //prepare the query and load the data adapter
            foreach (DataTable dt in ds.Tables)
            {
                //form the Insert command
                StringBuilder columnsList = new StringBuilder();
                string query = "INSERT INTO " + dt.TableName + " ";
                //Can't use an empty string to initialise so using the initial query which we will overwrite later.
                DbCommand command = db.GetSqlStringCommand(query);

                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.AutoIncrement == false && dc.ReadOnly == false) //if the call is set to auto-increment or it is read-only (perhaps a computed column)
                    {
                        DbType dbType = (DbType)DatabaseDBType[dc.DataType.FullName];
                        Debug.WriteLine(dc.DataType);

                        db.AddInParameter(command, "@" + dc.ColumnName, (DbType)DatabaseDBType[dc.DataType.FullName], dc.ColumnName, DataRowVersion.Current);
                        columnsList.AppendFormat(",@{0}", dc.ColumnName);
                    }
                }

                //Remove the first comma
                columnsList.Remove(0, 1);
                command.CommandText = query + "(" + columnsList.ToString().Replace('@', ' ') + ") VALUES (" + columnsList.ToString() + ") ";

                IDbDataAdapter da = db.GetDataAdapter();
                //Set up DataTableMapping
                da.TableMappings.Add("Table", dt.TableName);

                da.InsertCommand = command;
                da.InsertCommand.Connection = db.CreateConnection();
                da.Update(ds);
            }
        }

        #endregion // Static Methods

        public static void AddConstraints(DataSet ds, XmlNode xn)
        {

            foreach (XmlNode node in xn)
            {
                DataTable dt = ds.Tables[node.SelectSingleNode("//@name").InnerText];
                String keyName = node.SelectSingleNode("//@keyName").InnerText;
                String[] columns = node.SelectSingleNode("//PrimaryKeyColumns").InnerText.Split(new char[] { '|' });
                String[] dbGenerated = node.SelectSingleNode("//DBGeneratedColumns").InnerText.Split(new char[] { '|' });

                if (!(columns[0].Equals(String.Empty)))
                    AddUniqueConstraints(dt, columns, keyName);

                if (!dbGenerated[0].Equals(String.Empty))
                    MarkDBGeneratedFields(dt, node, dbGenerated);
            }
        }

        private static void AddUniqueConstraints(DataTable dt, string[] columns, string keyName)
        {
            DataColumn[] dataColumns = new DataColumn[columns.Length];
            int i = 0;
            foreach (string column in columns)
            {
                dataColumns[i] = dt.Columns[column];
                i++;
            }
            dt.Constraints.Add(new UniqueConstraint(keyName, dataColumns, true));
        }

        public static void MarkDBGeneratedFields(DataTable dt, XmlNode node, String[] dbGenerated)
        {
            foreach (string column in dbGenerated)
            {
                dt.Columns[column].AutoIncrement = true;
            }
        }
    }
}
